<?php
require_once 'database21.php';

class Score {
    private $db;
    
    public function __construct() {
        $this->db = new Database();
    }
    
    // 获取所有成绩
    public function getScores() {
        $sql = "SELECT s.score_id, s.student_id, s.course_id, s.score, 
                       st.student_name, c.course_name, cl.class_name
                FROM scores s 
                JOIN students st ON s.student_id = st.student_id 
                JOIN courses c ON s.course_id = c.course_id
                LEFT JOIN classes cl ON st.class_id = cl.class_id
                ORDER BY s.score_id";
        return $this->db->query($sql);
    }
    
    // 获取单个成绩
    public function getScore($id) {
        $sql = "SELECT * FROM scores WHERE score_id = ?";
        $result = $this->db->query($sql, [$id]);
        return $result ? $result[0] : null;
    }
    
    // 检查学生课程成绩是否已存在
    public function checkScoreExists($student_id, $course_id) {
        $sql = "SELECT COUNT(*) as count FROM scores WHERE student_id = ? AND course_id = ?";
        $result = $this->db->query($sql, [$student_id, $course_id]);
        return $result[0]['count'] > 0;
    }
    
    // 检查学生课程成绩是否已存在（排除当前记录）
    public function checkScoreExistsExcept($student_id, $course_id, $score_id) {
        $sql = "SELECT COUNT(*) as count FROM scores WHERE student_id = ? AND course_id = ? AND score_id != ?";
        $result = $this->db->query($sql, [$student_id, $course_id, $score_id]);
        return $result[0]['count'] > 0;
    }
    
    // 创建成绩
    public function createScore($student_id, $course_id, $score) {
        $sql = "INSERT INTO scores (student_id, course_id, score) VALUES (?, ?, ?)";
        return $this->db->execute($sql, [$student_id, $course_id, $score]);
    }
    
    // 更新成绩
    public function updateScore($id, $student_id, $course_id, $score) {
        $sql = "UPDATE scores SET student_id = ?, course_id = ?, score = ? WHERE score_id = ?";
        return $this->db->execute($sql, [$student_id, $course_id, $score, $id]);
    }
    
    // 删除成绩
    public function deleteScore($id) {
        $sql = "DELETE FROM scores WHERE score_id = ?";
        return $this->db->execute($sql, [$id]);
    }
    
    // 获取所有学生
    public function getStudents() {
        $sql = "SELECT s.student_id, s.student_name, c.class_name 
                FROM students s 
                LEFT JOIN classes c ON s.class_id = c.class_id 
                ORDER BY s.student_name";
        return $this->db->query($sql);
    }
    
    // 获取所有课程
    public function getCourses() {
        $sql = "SELECT c.course_id, c.course_name, t.teacher_name 
                FROM courses c 
                LEFT JOIN teachers t ON c.teacher_id = t.teacher_id 
                ORDER BY c.course_name";
        return $this->db->query($sql);
    }
    
    // 获取学生的所有成绩
    public function getStudentScores($student_id) {
        $sql = "SELECT s.score_id, s.score, c.course_name, c.credit, t.teacher_name
                FROM scores s 
                JOIN courses c ON s.course_id = c.course_id 
                LEFT JOIN teachers t ON c.teacher_id = t.teacher_id
                WHERE s.student_id = ?
                ORDER BY s.score_id";
        return $this->db->query($sql, [$student_id]);
    }
    
    // 计算学生的平均分
    public function calculateStudentAverage($student_id) {
        $sql = "SELECT AVG(score) as average FROM scores WHERE student_id = ?";
        $result = $this->db->query($sql, [$student_id]);
        return $result ? $result[0]['average'] : 0;
    }
    
    // 计算学生的总学分
    public function calculateStudentTotalCredits($student_id) {
        $sql = "SELECT SUM(c.credit) as total_credits 
                FROM scores s 
                JOIN courses c ON s.course_id = c.course_id 
                WHERE s.student_id = ? AND s.score >= 60";
        $result = $this->db->query($sql, [$student_id]);
        return $result ? $result[0]['total_credits'] : 0;
    }
}
?>